package cn.com.ulez.databasedemo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

/**
 *
 */
public class DBManager {

    private PicDatabaseHelper helper;
    private SQLiteDatabase database;

    public DBManager(Context context) {
        helper = new PicDatabaseHelper(context);
        database = helper.getWritableDatabase();
    }

    /**
     * 向表info中增加一个成员信息
     *
     * @param memberInfo
     */
    public void add(List<PhotoUpImageItem> memberInfo) {
        database.beginTransaction();// 开始事务
        try {
            for (PhotoUpImageItem info : memberInfo) {
                Log.i("LCY", "------add memberInfo----------");
                Log.i("LCY", info.getImagePath() + "/" + info.getAddress() + "/" + info.getMaxTemp() + "/" + info.getMinTemp());
                // 向表info中插入数据
                database.execSQL("INSERT INTO info VALUES(null,?,?,?,?,?)", new Object[]{info.getImageId(),info.getImagePath(), info.getAddress(), info.getMaxTemp(),
                        info.getMinTemp()});
            }
            database.setTransactionSuccessful();// 事务成功
        } finally {
            database.endTransaction();// 结束事务
        }
    }
//path VARCHAR, plalce INTEGER, maxtemp float,mintemp
    /**
     * @param _id
     * @param path
     * @param place
     * @param maxtemp
     * @param mintemp
     */
    public void add(int _id,int imageid, String path, String place, String maxtemp, String mintemp) {
        Log.i("LCY", "------add data----------");
        ContentValues cv = new ContentValues();
        // cv.put("_id", _id);
        cv.put("imageid", imageid);
        cv.put("path", path);
        cv.put("place", place);
        cv.put("maxtemp", maxtemp);
        cv.put("mintemp", mintemp);
        database.insert(PicDatabaseHelper.TABLE_NAME, null, cv);
        Log.i("LCY", path + "/" + place + "/" + maxtemp + "/" + mintemp);
    }

    /**
     * 通过path来删除数据
     *
     * @param path
     */
    public void delData(String path) {
        // ExecSQL("DELETE FROM info WHERE path ="+"'"+path+"'");
        String[] args = {path};
        database.delete(PicDatabaseHelper.TABLE_NAME, "path=?", args);
        Log.i("LCY", "delete data by " + path);

    }

    /**
     * 清空数据
     */
    public void clearData() {
        ExecSQL("DELETE FROM info");
        Log.i("LCY", "clear data");
    }

    /**
     * 通过path查询信息,返回所有的数据
     *
     * @param path
     */
    public ArrayList<PhotoUpImageItem> searchDataByPath(final String path) {
        String sql = "SELECT * FROM info WHERE path =" + "'" + path + "'";
        return ExecSQLForMemberInfo(sql);
    }
    /**
     * 通过imageid查询信息,返回所有的数据
     *
     * @param imageid
     */
    public ArrayList<PhotoUpImageItem> searchDataById(final int imageid) {
        String sql = "SELECT * FROM info WHERE imageid =" + "'" + imageid + "'";
        return ExecSQLForMemberInfo(sql);
    }

    public ArrayList<PhotoUpImageItem> searchAllData() {
        String sql = "SELECT * FROM info";
        return ExecSQLForMemberInfo(sql);
    }

    /**
     * 通过path来修改值
     *
     * @param raw
     * @param rawValue
     * @param wherepath
     */
    public void updateData(String raw, String rawValue, String wherepath) {
        String sql = "UPDATE info SET " + raw + " =" + " " + "'" + rawValue + "'" + " WHERE path =" + "'" + wherepath
                + "'";
        ExecSQL(sql);
        Log.i("LCY", sql);
    }

    /**
     * 执行SQL命令返回list
     *
     * @param sql
     * @return
     */
    private ArrayList<PhotoUpImageItem> ExecSQLForMemberInfo(String sql) {
        ArrayList<PhotoUpImageItem> list = new ArrayList<PhotoUpImageItem>();
        Cursor cursor = ExecSQLForCursor(sql);
        while (cursor.moveToNext()) {
            PhotoUpImageItem info = new PhotoUpImageItem();
            info.imageId = cursor.getInt(cursor.getColumnIndex("imageid"));
            info.imagePath = cursor.getString(cursor.getColumnIndex("path"));
            info.address = cursor.getString(cursor.getColumnIndex("place"));
            info.maxTemp = cursor.getDouble(cursor.getColumnIndex("maxtemp"));
            info.minTemp = cursor.getDouble(cursor.getColumnIndex("mintemp"));
            list.add(info);
        }
        cursor.close();
        return list;
    }

    /**
     * 执行一个SQL语句
     *
     * @param sql
     */
    private void ExecSQL(String sql) {
        try {
            database.execSQL(sql);
            Log.i("execSql: ", sql);
        } catch (Exception e) {
            Log.e("ExecSQL Exception", e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 执行SQL，返回一个游标
     *
     * @param sql
     * @return
     */
    private Cursor ExecSQLForCursor(String sql) {
        Cursor c = database.rawQuery(sql, null);
        return c;
    }

    public void closeDB() {
        database.close();
    }

}
